package com.bc.bccore.util;

import com.bc.commoncore.util.Assert;
import com.bc.commoncore.util.ParseHelper;
import com.bc.commoncore.util.ReflectionUtil;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelProcess {
	private Log logger = LogFactory.getLog(ExcelProcess.class);
	private static final ExcelProcess instance = new ExcelProcess();

	private ExcelProcess() {
	}
	public static <T> List<T> readExcel(byte[] buffers, int sheetIndex,int maxRow, Class<T> cls) throws Exception {
		Assert.notNull(buffers);
		List<T> list = new ArrayList<T>();
		// 目前只支持从第0列连续到n列的数据,而且第0行的数据为列标题(注意,不是表的标题)
		POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffers));
		HSSFWorkbook hwb = new HSSFWorkbook(fs);
		HSSFSheet sheet = hwb.getSheetAt(sheetIndex);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		rowNum = rowNum > maxRow ? maxRow : rowNum;

		for (int i = 1; i <= rowNum; i++) {
			HSSFRow row = sheet.getRow(i);
			if (row != null) {
				T obj = ReflectionUtil.getNewInstance(cls);
				Field[] fields = cls.getDeclaredFields();
				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					HSSFCell cell = row.getCell(j);
					if (cell != null) {
						String cellStringValue = getCellStringValue(cell);
						if (cellStringValue == null) {
							continue;
						}
						// 目前只支持String类型的映射
						ReflectionUtil.invokeSetterMethod(obj, field.getName(),cellStringValue);
					}
				}
				list.add(obj);
			}
		}
		return list;
	}

	/**
	 * 获得单元格字符串的值,目前只给定了几个类型,可以在此代码中扩展
	 * @author pengyi
	 * @date 2015年9月7日 下午4:19:48
	 * @version 1.0
	 * @param cell
	 * @return
	 */
	private static String getCellStringValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf(cell.getNumericCellValue());
			case HSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case HSSFCell.CELL_TYPE_FORMULA:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return ParseHelper.ToDateString(cell.getDateCellValue(),"yyyy-MM-dd HH:mm:ss");
				}
				// 如果是纯数字
				else {
					// 取得当前Cell的数值
					return String.valueOf(cell.getNumericCellValue());
				}
			default:
				return "";
		}
	}
	public static ExcelProcess getInstance() {
		return instance;
	}

	/**
	 * 导出模板
	 * 
	 * @param request
	 * @param response
	 * @param exportName
	 *            导出文件的文件名
	 * @param destFileIS
	 *            模板文件流
	 * @param destFileSuffix
	 *            模板文件后缀
	 * @param list
	 *            导出的数据
	 * @throws Exception
	 */
	public void exportExcel(HttpServletRequest request, HttpServletResponse response, String exportName,
                            InputStream destFileIS, String destFileSuffix, @SuppressWarnings("rawtypes") List list) throws Exception {
		// 加上文件后缀

		response.reset(); // 清空输出流
		response.setCharacterEncoding("utf-8");// 设置编码
		response.setContentType("application/vnd.ms-excel");
		response.addHeader("Content-Disposition", "attachment;filename=\"" + exportName + destFileSuffix + "\"");
		
		Map<String, Object> hashMap = new HashMap<>();
		hashMap.put("list", list);
		
		XLSTransformer transformer = new XLSTransformer();
		InputStream is = null;
		OutputStream os = null;
		try {
			is = new BufferedInputStream(destFileIS);
			Workbook workbook = transformer.transformXLS(is, hashMap);
			os = response.getOutputStream();
			workbook.write(os);
			os.flush();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					logger.error("ExcelProcess -> exportExcel() -> is.close() error!", e);
				}
			}
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
					logger.error("ExcelProcess -> exportExcel() -> os.close() error!", e);
				}
			}
		}
	}

}
